package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Employee;
import extend.org.compiere.utils.Util;

public class Mhrmsalaryemployee extends X_hrm_salaryemployee {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 4898200018391015934L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmsalaryemployee.class);

	public Mhrmsalaryemployee(Properties ctx, int hrm_salaryemployee_ID,
			String trxName) {
		super(ctx, hrm_salaryemployee_ID, trxName);
	}

	public Mhrmsalaryemployee(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

	private void updPeriod(PO po){
		// Upd period StartDate <-> EndDate
		Util.updPeriod(po, "C_BPartner_ID");
	}
	
	@Override
	public boolean save() {
		
		if(!super.save())
			throw new AdempiereException("");
		updPeriod(this);
		
		return true;
	}
	
	@Override
	public boolean delete(boolean force) {
		
		X_hrm_salaryemployee hrmsa = new X_hrm_salaryemployee(this.getCtx(), get_ID(), this.get_TrxName());
		
		boolean de = super.delete(force);
		// copy Values
		updPeriod(hrmsa);
		
		return de;
	}

	
	@Override
	protected boolean beforeSave(boolean newRecord) {

		//
		if(newRecord && Util.isOverlay(this, "c_bpartner_id"))
			throw new AdempiereException(Msg.translate(Env.getCtx(), "OverlayDates"));
		
		// Check for entry into a range of
		X_HR_Employee hremp = X_HR_Employee.getHR_Employee(getC_BPartner_ID(),null);
		X_hrm_salaryrange hrmsa = X_hrm_salaryrange.getSalaryRange(hremp.getHR_Job_ID());
		
		if(hrmsa.get_ID() == 0)
			throw new AdempiereException(Msg.translate(Env.getCtx(), "No range"));	
					
		if(gethrm_amountsalary().intValue() > hrmsa.gethrm_salarymax())
			throw new AdempiereException(Msg.translate(Env.getCtx(), "Salary out of range"));
		
		return true; 
	}	
	
	/** Load Model for HRM Salary Employee */
	public static Mhrmsalaryemployee getSalary(int C_BPartner_ID, Timestamp dt){
		return getSalary(C_BPartner_ID,dt, Env.getCtx());
	}
	
	public static Mhrmsalaryemployee getSalary(int C_BPartner_ID, Timestamp dt, Properties ctx){
		
		if(C_BPartner_ID == 0)
    		return null;
		
		Mhrmsalaryemployee hrmsa = null;		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT * FROM HRM_SALARYEMPLOYEE \n " + 
						"WHERE  C_BPARTNER_ID=" + C_BPartner_ID  + " \n " +
						"AND DATE_TRUNC('MONTH',"+DB.TO_DATE(dt)+") + INTERVAL '1 MONTH' - INTERVAL '1 DAY' BETWEEN STARTDATE AND ENDDATE";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				hrmsa = new Mhrmsalaryemployee(Env.getCtx(), rs, null);			
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return hrmsa;
	}
	
	/** Load Model for HRM Salary Employee */
	public static BigDecimal getAmountSalary(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT coalesce(ROUND(SUM(AMOUNTOTAL)),0) FROM HRM_EMPLOYEESALARY_V \n " +
						 "WHERE HOURQUOTA !=0 \n " +
						   "AND AD_CLIENT_ID="   + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						   "AND C_BPARTNER_ID="  + C_BPartner_ID + " \n " +
						   "AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
						   "AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + " \n " ;
		try
		{
			pstmt = DB.prepareStatement(sql_,null);						
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	/** Select work days for HRM Salary Employee */
	public static int getSalaryDays(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return 0;
				
		int result = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT COUNT(HOURQUOTA) FROM HRM_EMPLOYEESALARY_V \n " +
						 "WHERE HOURQUOTA !=0 \n " +
						   "AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						   "AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						   "AND DATEFROM BETWEEN DATE_TRUNC('MONTH'::TEXT," + DB.TO_DATE(dt) + ") AND DATE_TRUNC('MONTH'::TEXT," + DB.TO_DATE(dt) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";		                                                                                             
		try
		{
			pstmt = DB.prepareStatement(sql_,null);			
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getInt(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}
	
	/** Select work hour for HRM Salary Employee */
	public static int getSalaryHours(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return 0;
				
		int result = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT coalesce(SUM(HOURQUOTA),0) FROM HRM_EMPLOYEESALARY_V \n " +
						 "WHERE HOURQUOTA !=0 \n " +
						   "AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						   "AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						   "AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
						   "AND (DATE_TRUNC('MONTH',DATEFROM) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::DATE >=" + DB.TO_DATE(dt, true);
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getInt(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}

public static BigDecimal getFullSalary(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return null;
			
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	BigDecimal result = new BigDecimal(0);
	
	// Employee data from the card
	String sql_ = "SELECT SUM(TOTAL) FROM HRM_EMPLOYEESALARY_V \n " +
					 "WHERE HOURQUOTA !=0 \n " +
					     "AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					     "AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					     "AND DATEFROM BETWEEN DATE_TRUNC('MONTH'::TEXT," + DB.TO_DATE(dt) + ") AND DATE_TRUNC('MONTH'::TEXT," + DB.TO_DATE(dt) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
	try
	{
		pstmt = DB.prepareStatement(sql_,null);		
		rs = pstmt.executeQuery();
		if(rs.next())
			result = rs.getBigDecimal(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}
	
	return (result == null) ? new BigDecimal(0) : result;
    }



public static BigDecimal getAmount(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return null;
	
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	BigDecimal result = new BigDecimal(0);
	
	// Employee data from the card
	String sql_ = "SELECT HRM_AMOUNTSALARY FROM HRM_SALARYEMPLOYEE \n " +
					"WHERE AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					 "AND C_BPARTNER_ID=" + C_BPartner_ID  + " \n " +
					 " AND " + DB.TO_DATE(dt) + " BETWEEN STARTDATE AND ENDDATE";
	try
	{
		pstmt = DB.prepareStatement(sql_,null);
		rs = pstmt.executeQuery();
		if(rs.next())
			result = rs.getBigDecimal(1);			
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}
	
	return (result == null) ? new BigDecimal(0) : result;
  }

}